<?php
class Karyawan_history_jabatan_model extends MY_Model {
	
    function Karyawan_history_jabatan_model(){
        parent::MY_Model();
        $this->load->database();
        $this->table_name = "karyawan_history_jabatan";
        $this->table_fields = array('kary_jbt_id','kary_jbt_tgl_menjabat','kary_jbt_tgl_berhenti',
        	'kary_id','jbt_id');
    }
    
    /**
     * 
     * @param $id
     */
    function select_by_id($id){
    	$this->join('jabatan', 'jabatan.jbt_id='. $this->table_name.'.jbt_id');
    	$this->join('departemen', 'departemen.dep_id=jabatan.dep_id');
    	$this->join('karyawan', 'karyawan.kary_id='.$this->table_name.'.kary_id');
    	return $this->get_where(array('kary_jbt_id' => $id));
    }
    
    function getActiveJbt($kary_id, $jbt_id){
    	$now = date("Y-m-d",time());
    	
    	$res = $this->getHistory($kary_id, false, 
    		"'$now' BETWEEN a.kary_jbt_tgl_menjabat AND a.kary_jbt_tgl_berhenti AND a.jbt_id='$jbt_id'",
    		10,0,"jbt_nama ASC, dep_nama ASC");
    	
    	if (!$res->num_rows()){
    		$res = $this->getHistory($kary_id, false, "c.dep_default=1 AND a.jbt_id='$jbt_id'", 10, 0, 
    			"jbt_nama ASC, dep_nama ASC");
    	}
    	
    	return $res;
    }
    
    function getActive($kary_id){
    	$now = date("Y-m-d",time());
    	$res = $this->getHistory($kary_id, false, 
    		"'$now' BETWEEN a.kary_jbt_tgl_menjabat AND a.kary_jbt_tgl_berhenti",
    		10,0,"jbt_nama ASC, dep_nama ASC");
    		
    	if (!$res->num_rows()){
    		$res = $this->getHistory($kary_id, false, "c.dep_default=1", 10, 0, 
    			"jbt_nama ASC, dep_nama ASC");
    	}
    	return $res;
    }
    
    function getKaryawanActive($jbt_id){
    	$now = date("Y-m-d",time());
    	
    	//karena terdapat jabatan default sebagai staff
    	$this->where("
    		IF(jabatan.dep_default=0,
    		'$now' BETWEEN kary_jbt_tgl_menjabat AND kary_jbt_tgl_berhenti,
    		1)");
    	$this->where($this->table_name.".jbt_id='$jbt_id'");
    	
    	$this->join('jabatan','jabatan.jbt_id='.$this->table_name.'.jbt_id');
    	$data = $this->get();
    	$data = $data->row();
    	if ($data) return $data->kary_id;
    	return  false;
    }

    function getHistory($kary_id, $key=false, $where=false, $limit=10, $offset=0, $orderby=false){
 		/**
 		 * THE SQL:
 		 * 		SELECT a.*, b.kary_nama, c.jbt_nama 
 		 * 		FROM `karyawan_history_jabatan` as a, karyawan as b, jabatan as c, d.departemen
 		 * 		WHERE a.kary_id = b.kary_id and a.jbt_id = c.jbt_id
 		 */
 		if ($key){
 			$key = "AND (
			  a.`kary_jbt_id` LIKE '%$key%' OR
			  a.`kary_jbt_tgl_menjabat` LIKE '%$key%' OR
			  a.`kary_jbt_tgl_berhenti` LIKE '%$key%' OR
			  a.`kary_id` LIKE '%$key%' OR
			  a.`jbt_id` LIKE '%$key%')";
 		}
 		
 		if ($where) { 
 			if (is_array($where)){
 				$oo = "";
 				foreach(array_keys($where) as $rr){
 					$oo .= ($oo? " AND " : ""). "$rr ".$where[$rr];
 				}
 				$where = "$oo ";
 			}else{
 				$where = " AND $where";
 			}
 		}
 	
 		if ($orderby){ 
 			if (is_array($orderby)){
 				$oo = "";
 				foreach(array_keys($orderby) as $rr){
 					$oo .= ($oo? "," : ""). "$rr ".$orderby[$rr];
 				}
 				$orderby = "ORDER BY $oo ";
 			}else{
 				$orderby = "ORDER BY $orderby";
 			} 
 		}
 		
 		$limit = " LIMIT $offset,$limit ";  
 		
    	return $this->db->query(
    		"SELECT a.*, b.kary_nama, c.jbt_nama, d.dep_nama
 		  	FROM `karyawan_history_jabatan` as a, `karyawan` as b, `jabatan` as c, `departemen` as d
 		  	WHERE a.kary_id = b.kary_id 
 		  		AND a.jbt_id = c.jbt_id 
 		  		AND c.dep_id = d.dep_id
 		  		AND a.kary_id='$kary_id'
 		  		$key 
 		  		$where
 		  		$orderby 
 		  		$limit");
    }
    
    /**
     * return history jabatan karyawan dengan status aktif/non aktif untuk semua jabatan (termasuk jabatan default)
     * @param $kary_id
     * @param $key
     * @param $where
     * @param $limit
     * @param $offset
     * @param $orderby
     */
	function getHistory2($kary_id, $key=false, $where=false, $limit=10, $offset=0, $orderby=false){
		$now = date("Y-m-d",time());
 		/**
 		 * THE SQL:
 		 * 		SELECT a.*, b.kary_nama, c.jbt_nama 
 		 * 		FROM `karyawan_history_jabatan` as a, karyawan as b, jabatan as c, d.departemen
 		 * 		WHERE a.kary_id = b.kary_id and a.jbt_id = c.jbt_id
 		 */
 		if ($key){
 			$key = "AND (
			  a.`kary_jbt_id` LIKE '%$key%' OR
			  a.`kary_jbt_tgl_menjabat` LIKE '%$key%' OR
			  a.`kary_jbt_tgl_berhenti` LIKE '%$key%' OR
			  a.`kary_id` LIKE '%$key%' OR
			  a.`jbt_id` LIKE '%$key%')";
 		}
 		
 		if ($where) { 
 			if (is_array($where)){
 				$oo = "";
 				foreach(array_keys($where) as $rr){
 					$oo .= ($oo? " AND " : ""). "$rr ".$where[$rr];
 				}
 				$where = "$oo ";
 			}else{
 				$where = " AND $where";
 			}
 		}
 	
 		if ($orderby){ 
 			if (is_array($orderby)){
 				$oo = "";
 				foreach(array_keys($orderby) as $rr){
 					$oo .= ($oo? "," : ""). "$rr ".$orderby[$rr];
 				}
 				$orderby = "ORDER BY $oo ";
 			}else{
 				$orderby = "ORDER BY $orderby";
 			} 
 		}
 		
 		$limit = " LIMIT $offset,$limit ";  
 		
    	return $this->db->query(
    		"SELECT *,
    			IF('$now' BETWEEN a.kary_jbt_tgl_menjabat AND a.kary_jbt_tgl_berhenti AND c.dep_default=0, 'AKTIF',
    				IF(c.dep_default=1,'AKTIF','NON AKTIF')) as status
 		  	FROM `karyawan_history_jabatan` as a, `karyawan` as b, `jabatan` as c, `departemen` as d
 		  	WHERE a.kary_id = b.kary_id 
 		  		AND a.jbt_id = c.jbt_id 
 		  		AND c.dep_id = d.dep_id
 		  		AND a.kary_id='$kary_id'
 		  		$key 
 		  		$where
 		  		$orderby 
 		  		$limit");
    }
}